package com.hnkywl.sts.dao.cwgl;

import com.hnkywl.sts.dao.base.RegionDao;
import com.hnkywl.sts.dao.cwgl.kmys.CykmdmysDao;
import com.hnkywl.sts.entity.base.Sjzdz;
import com.hnkywl.sts.entity.cwgl.VoucherItemData;
import com.hnkywl.sts.entity.cwgl.kmys.Cykmdmys;
import com.hnkywl.sts.entity.organ.Gsjg;
import com.hnkywl.sts.entity.organ.Nbgs;
import com.hnkywl.sts.entity.organ.Qyjg;
import com.hnkywl.sts.entity.sfgl.Sfk;
import com.hnkywl.sts.service.organ.GsjgService;
import com.hnkywl.sts.service.organ.QyjgService;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import oracle.jdbc.OracleCallableStatement;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.transform.AliasToEntityMapResultTransformer;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.*;
import java.util.Date;

/**
 * Created with IntelliJ IDEA.
 * User: Administrator
 * Date: 13-5-14
 * Time: 上午9:46
 * To change this template use File | Settinsbgs | File Templates.
 */
@Repository
public class CwsfDao extends SimpleHibernateTemplate<Sfk, Long> {
    private CykmdmysDao cykmdmysDao;
    private GsjgService gsjgService;

    public Page<Sfk> findCwsfList(Integer sfklx, String zt, String bm, Date sfkrqStart, Date sfkrqEnd, String sbgsMc, Long sbgsId, String gldwMc, String cwsfpjlxMc,Integer cwsfZt, int pageNo, int pageSize) {
        String hql = null;
        List<Object> param = new ArrayList<Object>();
        StringBuilder condition = new StringBuilder();
        Page<Sfk> page = new Page<Sfk>(pageNo, pageSize, 0);
        //==============条件拼接===============
        if (sbgsId != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" sbgs.id = ? ");
            param.add(sbgsId);
        }
        if (sbgsId == null && sbgsMc != null && !sbgsMc.equals("")) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" sbgs.jc like ? ");
            param.add("%" + sbgsMc.trim() + "%");
        }
        if (zt != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" zt = ? ");
            param.add(zt);
        }
        if (bm != null && !bm.isEmpty()) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" bm like ? ");
            param.add("%" + bm.trim() + "%");
        }
        if (sfkrqStart != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" sfkrq >= ?");
            param.add(sfkrqStart);
        }
        if (sfkrqEnd != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" sfkrq <= ?");
            param.add(sfkrqEnd);
        }
        if (gldwMc != null && !"".equals(gldwMc)) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" gldw.qc like ? ");
            param.add("%" + gldwMc.trim() + "%");
        }
        if (cwsfpjlxMc != null && !"".equals(cwsfpjlxMc)) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" pjlx.z like ? ");
            param.add("%" + cwsfpjlxMc.trim() + "%");
        }
        if (cwsfZt != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            //status=0未导入
            //status=1导入成功
            //status=2导入失败
            //status=3删除成功
            //status=4删除失败
            condition.append(" ("+cwsfZt+"=0 and insertFlag=1) " +
                    "or ("+cwsfZt+"=1 and insertFlag=3 and deleteFlag!=2) " +
                    "or ("+cwsfZt+"=2 and insertFlag=2) " +
                    "or ("+cwsfZt+"=3 and deleteFlag=3 and insertFlag!=2) " +
                    "or ("+cwsfZt+"=4 and deleteFlag=2) " );
        }
        //==============记录数量===============
        String sfk = " " + sfklx + "," + (sfklx + 1);
        hql = " select count(*) from Sfk where sfklx in (" + sfk + ") and kxyt.z !='充值' " +
                " and (createUser.id is not null or (createUser.id is null and sbgs.id  in (select id from Nbgs))) " +
                " and sfBzj != 1 "+
                  RegionDao.createDataRegion("and");
        if (condition.length() != 0)
            hql = hql + " and " + condition;
        Long c = null;
        if (param.size() != 0) {
            c = (Long) findUnique(hql, param.toArray());
            page.setTotalCount(c.intValue());
        } else {
            c = (Long) findUnique(hql);
            page.setTotalCount(c.intValue());
        }
        if (c == 0)
            return page;
        //==============查询结果===============
        hql = "from Sfk where sfklx in (" + sfk + ")"  +
                " and (createUser.id is not null or (createUser.id is null and sbgs.id  in (select id from Nbgs))) " +
                " and sfBzj != 1 and kxyt.z !='充值' " +
                RegionDao.createDataRegion("and");
        if (condition.length() != 0)
            hql = hql + " and " + condition;
        hql = hql + " order by dateCreated desc";
        if (param.size() != 0)
            return find(page, hql, param.toArray());
        return find(page, hql);
    }



    /**
     * 获得财务收付第一条分录
     *
     * @return
     */
    private String getCwsfSq1() {
        String sql = "   select (case when sfk.kxlx = 1  then '付' || gldw.qc || '预付款' when sfk.kxlx = 2  then '付' || gldw.qc || '应付款' when sfk.kxlx = 3  then '收' || gldw.qc || '预收款'" +
                "                else '收' || gldw.qc || '应收款' end ) as  \"digest\", " +
                "                1   as \"id\"," +
                "                1 as \"itemorder\" ," +
                "      (case when sfk.kxlx in (1,2) and sfk.gldw_id in (select  id from t_sys_gsjg where type ='nbgs' and qyfr_id is not null ) then " +
                "             (select kmdm from t_sys_cykmdmys cykmdmys where cykmdmys.codeid =3400 and gsid = sbgs.id)  " +
                "       when sfk.kxlx in (1,2) and sfk.gldw_id in (select  id from t_sys_gsjg where type ='nbgs' and qyfr_id is  null ) then " +
                "             (select kmdm from t_sys_cykmdmys cykmdmys where cykmdmys.codeid =3500 and gsid = sbgs.id)  " +
                "      when sfk.kxlx in (1,2) and sjzdz.z='银票'   then        " +
                "             (select kmdm from t_sys_cykmdmys cykmdmys where cykmdmys.codeid =300 and gsid = sbgs.id) " +
                "      when sfk.kxlx in (1,2) and sjzdz.z='商票'   then                         " +
                "             (select kmdm from t_sys_cykmdmys cykmdmys where cykmdmys.codeid =300 and gsid = sbgs.id) " +
                "      when sfk.kxlx in (1,2) and sjzdz.z='信用证'   then                        " +
                "             (select kmdm from t_sys_cykmdmys cykmdmys where cykmdmys.codeid =300 and gsid = sbgs.id)   " +
                "      when sfk.kxlx in (1,2) and sjzdz.z != '信用证' and sjzdz.z != '商票' and sjzdz.z != '银票'  then   " +
                "             (select kmdm from t_sys_cykmdmys cykmdmys where cykmdmys.codeid =300 and gsid = sbgs.id)  " +
                "      when sfk.kxlx in (3,4) and sfk.gldw_id in (select  id from t_sys_gsjg where type ='nbgs' and qyfr_id is not null ) then " +
                "             khh.kmdm           " +                                                                                        //内部公司收款的分录
                "      when sfk.kxlx in (3,4) and sfk.gldw_id in (select  id from t_sys_gsjg where type ='nbgs' and qyfr_id is null ) then " +
                "             khh.kmdm           " +
                "      when kxlx in (3,4) and sjzdz.z='银票'   then                              " +
                "             (select kmdm from t_sys_cykmdmys cykmdmys where cykmdmys.codeid =600 and gsid = sbgs.id)  " +
                "      when kxlx in (3,4) and sjzdz.z='商票'   then                             " +
                "             (select kmdm from t_sys_cykmdmys cykmdmys where cykmdmys.codeid =601 and gsid = sbgs.id)  " +
                "      when kxlx in (3,4) and sjzdz.z='信用证'   then                          " +
                "             khh.kmdm           " +
                "      when kxlx in (3,4) and sjzdz.z != '信用证' and sjzdz.z != '商票' and sjzdz.z != '银票'  then      " +
                "             khh.kmdm           " +
                "      else '收' || gldw.qc || '款' end )   as \"subjectcode\"," +
                "      (case when sfk.kxlx in (1,2) and sfk.gldw_id in (select  id from t_sys_gsjg where type ='nbgs' and qyfr_id is not null ) then " +
                "             (select jqcwkmmc from t_sys_cykmdmys cykmdmys where cykmdmys.codeid =3400 and gsid = sbgs.id)  " +
                "       when sfk.kxlx in (1,2) and sfk.gldw_id in (select  id from t_sys_gsjg where type ='nbgs' and qyfr_id is  null ) then " +
                "             (select jqcwkmmc from t_sys_cykmdmys cykmdmys where cykmdmys.codeid =3500 and gsid = sbgs.id)  " +
                "      when sfk.kxlx in (1,2) and sjzdz.z='银票'   then " +
                "             (select jqcwkmmc from t_sys_cykmdmys cykmdmys where cykmdmys.codeid =300 and gsid = sbgs.id)  " +
                "      when sfk.kxlx in (1,2) and sjzdz.z='商票'   then " +
                "             (select jqcwkmmc from t_sys_cykmdmys cykmdmys where cykmdmys.codeid =300 and gsid = sbgs.id)  " +
                "      when sfk.kxlx in (1,2) and sjzdz.z='信用证'   then " +
                "           (select jqcwkmmc from t_sys_cykmdmys cykmdmys where cykmdmys.codeid =300 and gsid = sbgs.id)" +
                "      when sfk.kxlx in (1,2) and sjzdz.z != '信用证' and sjzdz.z != '商票' and sjzdz.z != '银票'  then " +
                "             (select jqcwkmmc from t_sys_cykmdmys cykmdmys where cykmdmys.codeid =300 and gsid = sbgs.id) " +
                "      when sfk.kxlx in (3,4) and sfk.gldw_id in (select  id from t_sys_gsjg where type ='nbgs' and qyfr_id is not null ) then " +
                "             '银行存款/' || khh.qc            " +
                "      when sfk.kxlx in (3,4) and sfk.gldw_id in (select  id from t_sys_gsjg where type ='nbgs' and qyfr_id is  null ) then " +
                "             '银行存款/' || khh.qc            " +
                "      when kxlx in (3,4) and sjzdz.z='银票'   then  " +
                "             (select jqcwkmmc from t_sys_cykmdmys cykmdmys where cykmdmys.codeid =600 and gsid = sbgs.id)  " +
                "      when kxlx in (3,4) and sjzdz.z='商票'   then " +
                "             (select jqcwkmmc from t_sys_cykmdmys cykmdmys where cykmdmys.codeid =601 and gsid = sbgs.id)  " +
                "      when kxlx in (3,4) and sjzdz.z='信用证'   then " +
                "              '银行存款/' || khh.qc            " +
                "      when kxlx in (3,4) and sjzdz.z != '信用证' and sjzdz.z != '商票' and sjzdz.z != '银票'  then " +
                "             '银行存款/' || khh.qc            " +
                "                else '收' || gldw.qc || '款' end ) as \"subjectname\" ," +
                "  (case when sfk.kxlx in (1,3)  then 0  when sfk.kxlx  in(2,4)  then  sfk.sfje " +
                "                 end ) as  \"debit\", " +
                "  (case when sfk.kxlx in (1,3)  then sfk.sfje  when sfk.kxlx  in(2,4)  then  0 " +
                "                 end ) as  \"credit\", " +
                "                ''            as    \"qty\"," +
                "                0           as  \"price\"," +
                "                'CNY'         as  \"currencycode\"   ," +
                "                '1.000000'         as  \"exchrate\"   ," +
                "  (case when sfk.kxlx in (1,3)  then 0  when sfk.kxlx  in(2,4)  then  sfk.sfje " +
                "                 end ) as  \"orgnd\", " +
                "  (case when sfk.kxlx in (1,3)  then sfk.sfje  when sfk.kxlx  in(2,4)  then  0 " +
                "                 end ) as  \"orgnc\", " +
                "                 ''           as  \"settlementcode\"," +
                "                 ''           as  \"settlementno\"," +
                "                 ''           as  \"bizno\"," +
                "      (case when sfk.kxlx in (1,2)   then   " +
                "          sfk.gldw.qc " +
                "        end )     as  \"suppliername\"," +
                "      (case when sfk.kxlx in (1,2)  then   " +
                "          (select kmdm from t_sys_ljkhdmys where nbgsid =sfk.sbgs_id  and qyjgid =sfk.gldw_id and lx =0 ) " +
                "        end )     as  \"suppliercode\" ," +//供应商
                "       (case when  (sjzdz.z = '商票' or sjzdz.z = '银票')  and kxlx in (3,4) then   " +
                "          sfk.gldw.qc       " +
                "        end )     as  \"customername\"," +//客户
                "       (case when  (sjzdz.z = '商票' or sjzdz.z = '银票')  and kxlx in (3,4) then   " +
                "          (select kmdm from t_sys_ljkhdmys where nbgsid =sfk.sbgs_id  and qyjgid =sfk.gldw_id and lx =1 )       " +
                "        end )     as  \"customercode\"," +
                "       ''                as \"departmentname\"," +
                "       ''                as \"departmentcode\"," +
                "       ''                as \"staffname\"," +
                "       ''                as \"staffcode\"                             " +
                "                 from t_biz_sfk sfk " +
                " left join t_sys_gsjg gldw on gldw.id = sfk.gldw_id" +
                " left join t_sys_gsjg sbgs on sbgs.id = sfk.sbgs_id" +
                " left join t_sys_sjzdz sjzdz on sfk.pjlx_sjzdz_id = sjzdz.id" +
                " left join t_sys_khh khh on khh.id = sfk.glkhh_id" +
                " where sfk.deleted = 0 and sfk.zt = 3 and sfk.id = :sfkId ";
        return sql;
    }

    /**
     * 财务收付第二条分录
     *
     * @return
     */
    private String getCwsfSq2() {
        String sql = "   select (case when sfk.kxlx = 1  then '付' || gldw.qc || '预付款' when sfk.kxlx = 2  then '付' || gldw.qc || '应付款' when sfk.kxlx = 3  then '收' || gldw.qc || '预收款'" +
                "                else '收' || gldw.qc || '应收款' end ) as  \"digest\", " +
                "                1   as  \"id\"," +
                "                1 as \"itemorder\" ," +
                "      (case when sfk.kxlx in (1,2) and sfk.gldw_id in (select  id from t_sys_gsjg where type ='nbgs' and qyfr_id is not  null ) then " +
                "             khh.kmdm " +
                "       when sfk.kxlx  in (1,2) and sfk.gldw_id in (select  id from t_sys_gsjg where type ='nbgs' and qyfr_id is   null ) then " +
                "             khh.kmdm " +
                "      when sfk.kxlx  in (1,2) and sjzdz.z='银票'   then          " +
                "             (select kmdm from t_sys_cykmdmys cykmdmys where cykmdmys.codeid =600 and gsid = sbgs.id)  " +
                "      when sfk.kxlx  in(1,2) and sjzdz.z='商票'   then                          " +
                "             (select kmdm from t_sys_cykmdmys cykmdmys where cykmdmys.codeid =601 and gsid = sbgs.id)  " +
                "      when sfk.kxlx in (1,2) and sjzdz.z='信用证'   then                        " +
                "             (select kmdm from t_sys_cykmdmys cykmdmys where cykmdmys.codeid =3000 and gsid = sbgs.id)  " +
                "      when sfk.kxlx in (1,2) and sjzdz.z != '信用证' and sjzdz.z != '商票' and sjzdz.z != '银票'  then    " +
                "             khh.kmdm" +
                "      when  sfk.kxlx in (3,4) and sfk.gldw_id in (select  id from t_sys_gsjg where type ='nbgs' and qyfr_id is not  null ) then    " +
                "            (select kmdm from t_sys_cykmdmys cykmdmys where cykmdmys.codeid =3400 and gsid = sbgs.id)    " +
                "      when  sfk.kxlx in (3,4) and sfk.gldw_id in (select  id from t_sys_gsjg where type ='nbgs' and qyfr_id is   null ) then    " +
                "            (select kmdm from t_sys_cykmdmys cykmdmys where cykmdmys.codeid =3500 and gsid = sbgs.id)    " +
                "      when     kxlx in (3,4) and sjzdz.z='银票'   then                              " +
                "             (select kmdm from t_sys_cykmdmys cykmdmys where cykmdmys.codeid =100 and gsid = sbgs.id)  " +
                "      when kxlx in (3,4) and sjzdz.z='商票'   then                             " +
                "             (select kmdm from t_sys_cykmdmys cykmdmys where cykmdmys.codeid =100 and gsid = sbgs.id) " +
                "      when kxlx in (3,4) and sjzdz.z='信用证'   then                          " +
                "            (select kmdm from t_sys_cykmdmys cykmdmys where cykmdmys.codeid =100 and gsid = sbgs.id)    " +
                "      when kxlx in (3,4) and sjzdz.z != '信用证' and sjzdz.z != '商票' and sjzdz.z != '银票'  then      " +
                "           (select kmdm from t_sys_cykmdmys cykmdmys where cykmdmys.codeid =100 and gsid = sbgs.id)           " +
                "      else '收' || gldw.qc || '款' end )   as \"subjectcode\"," +
                "      (case when sfk.kxlx in (1,2) and sfk.gldw_id in (select  id from t_sys_gsjg where type ='nbgs' and qyfr_id is not  null ) then " +
                "             '银行存款/' || khh.qc   " +
                "       when sfk.kxlx in (1,2) and sfk.gldw_id in (select  id from t_sys_gsjg where type ='nbgs' and qyfr_id is   null ) then " +
                "             '银行存款/' || khh.qc   " +
                "      when sfk.kxlx in (1,2) and sjzdz.z='银票'   then          " +
                "             (select jqcwkmmc from t_sys_cykmdmys cykmdmys where cykmdmys.codeid =600 and gsid = sbgs.id)   " +
                "      when sfk.kxlx in (1,2) and sjzdz.z='商票'   then " +
                "             (select jqcwkmmc from t_sys_cykmdmys cykmdmys where cykmdmys.codeid =601 and gsid = sbgs.id)  " +
                "      when sfk.kxlx in (1,2) and sjzdz.z='信用证'   then " +
                "             (select jqcwkmmc from t_sys_cykmdmys cykmdmys where cykmdmys.codeid =3000 and gsid = sbgs.id)  " +
                "      when sfk.kxlx in (1,2) and sjzdz.z != '信用证' and sjzdz.z != '商票' and sjzdz.z != '银票'  then " +
                "             '银行存款/' || khh.qc   " +
                "      when  sfk.kxlx in (3,4) and sfk.gldw_id in (select  id from t_sys_gsjg where type ='nbgs' and qyfr_id is not  null ) then    " +
                "            (select jqcwkmmc from t_sys_cykmdmys cykmdmys where cykmdmys.codeid =3400 and gsid = sbgs.id)    " +
                "      when  sfk.kxlx in (3,4) and sfk.gldw_id in (select  id from t_sys_gsjg where type ='nbgs' and qyfr_id is   null ) then    " +
                "            (select jqcwkmmc from t_sys_cykmdmys cykmdmys where cykmdmys.codeid =3500 and gsid = sbgs.id)    " +
                "      when     kxlx in (3,4) and sjzdz.z='银票'   then                              " +
                "             (select jqcwkmmc from t_sys_cykmdmys cykmdmys where cykmdmys.codeid =100 and gsid = sbgs.id) " +
                "      when kxlx in (3,4) and sjzdz.z='商票'   then " +
                "             (select jqcwkmmc from t_sys_cykmdmys cykmdmys where cykmdmys.codeid =100 and gsid = sbgs.id)  " +
                "      when kxlx in (3,4) and sjzdz.z='信用证'   then " +
                "            (select jqcwkmmc from t_sys_cykmdmys cykmdmys where cykmdmys.codeid =100 and gsid = sbgs.id)  " +
                "      when kxlx in (3,4) and sjzdz.z != '信用证' and sjzdz.z != '商票' and sjzdz.z != '银票'  then " +
                "            (select jqcwkmmc from t_sys_cykmdmys cykmdmys where cykmdmys.codeid =100 and gsid = sbgs.id)  " +
                "                      " +
                "                else '收' || gldw.qc || '款' end ) as \"subjectname\" ," +
                "  (case when sfk.kxlx in (1,3)  then sfk.sfje   when sfk.kxlx  in(2,4)  then  0 " +
                "                 end ) as  \"debit\", " +
                "  (case when sfk.kxlx in (1,3)  then 0  when sfk.kxlx  in(2,4)  then  sfk.sfje  " +
                "                 end ) as  \"credit\", " +
                "                ''            as \"qty\"," +
                "                0           as \"price\"," +
                "                'CNY'         as \"currencycode\"   ," +
                "                '1.000000'         as  \"exchrate\"   ," +
                "  (case when sfk.kxlx in (1,3)  then sfk.sfje   when sfk.kxlx  in(2,4)  then  0 " +
                "                 end ) as  \"orgnd\", " +
                "  (case when sfk.kxlx in (1,3)  then 0  when sfk.kxlx  in(2,4)  then  sfk.sfje  " +
                "                 end ) as  \"orgnc\", " +
                "                 ''           as \"settlementcode\"," +
                "                 ''           as \"settlementno\"," +
                "                 ''           as \"bizno\"," +
                "       (case when  sjzdz.z = '信用证' and kxlx in (1,2)  then  " +
                "          sfk.gldw.qc       " +
                "        when  kxlx in (3,4) and sfk.gldw_id  in (select  id from t_sys_gsjg where type ='nbgs'  )   then  " +
                "          sfk.gldw.qc       " +
                "        end )     as \"suppliername\"," +//供应商
                "       (case when kxlx in (1,2) and  sjzdz.z = '信用证'    then  " +
                "          (select kmdm from t_sys_ljkhdmys where nbgsid =sfk.sbgs_id  and qyjgid =sfk.gldw_id and lx =0 )       " +
                "        when kxlx in (3,4) and  sfk.gldw_id  in (select  id from t_sys_gsjg where type ='nbgs'  )   then  " +
                "          (select kmdm from t_sys_ljkhdmys where nbgsid =sfk.sbgs_id  and qyjgid =sfk.gldw_id and lx =0 )       " +
                "        end )     as \"suppliercode\"," +//供应商
                "      (case when kxlx in (3,4) and sfk.gldw_id not in (select  id from t_sys_gsjg where type ='nbgs'  )  then   " +
                "          sfk.gldw.qc  " +
                "          when   sjzdz.z = '商票' or sjzdz.z = '银票'   then   " +
                "          sfk.gldw.qc         " +
                "        end )     as \"customername\"," +//客户
                "      (case when kxlx in (3,4) and sfk.gldw_id not in (select  id from t_sys_gsjg where type ='nbgs'  )  then   " +
                "          (select kmdm from t_sys_ljkhdmys where nbgsid =sfk.sbgs_id  and qyjgid =sfk.gldw_id and lx =1 ) " +
                "       when    sjzdz.z = '商票' or sjzdz.z = '银票'   then   " +
                "          (select kmdm from t_sys_ljkhdmys where nbgsid =sfk.sbgs_id  and qyjgid =sfk.gldw_id and lx =1 ) " +
                "        end )     as \"customercode\" ," +//客户
                "       ''                as \"departmentname\"," +
                "       ''                as \"departmentcode\"," +
                "       ''                as \"staffname\"," +
                "       ''                as \"staffcode\"                             " +
                "                 from t_biz_sfk sfk " +
                " left join t_sys_gsjg gldw on gldw.id = sfk.gldw_id" +
                " left join t_sys_gsjg sbgs on sbgs.id = sfk.sbgs_id" +
                " left join t_sys_sjzdz sjzdz on sfk.pjlx_sjzdz_id = sjzdz.id" +
                " left join t_sys_khh khh on khh.id = sfk.glkhh_id" +
                " where sfk.deleted = 0 and sfk.zt = 3 and sfk.id = :sfkId";
        return sql;
    }

    public List<Map> getVoucher(Sfk cwsf) {
        Gsjg jg  = gsjgService.findGsjgDetail(cwsf.getGldw().getId());
         Connection con = null;
        CallableStatement call = null;
        ResultSet rs = null;
        try {
            con = getSession().connection();
            if(jg.className()=="Nbgs"){
                call = con.prepareCall("{Call VOUCHER_NBCWSF(?,?)}");
            }else if(jg.className()=="Qyjg"){
                call = con.prepareCall("{Call VOUCHER_CWSF(?,?)}");
            }
            call.setLong(1, cwsf.getId());
            call.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
            call.execute();
            //====================================
            rs = ((OracleCallableStatement) call).getCursor(2);
            ResultSetMetaData resultsMetaData = rs.getMetaData();
            int columnCount = resultsMetaData.getColumnCount();
            List<Map> result = new ArrayList<Map>();
            while (rs.next()) {
                Map<String, Object> r = new HashMap<String, Object>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = resultsMetaData.getColumnName(i).toLowerCase();
                    r.put(columnName, rs.getObject(i));
                }
                result.add(r);
            }
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        } finally {
            closeResource(rs, call, con);
        }
    }

    public String revokeNote(Long id, Integer revokeType, Long userId) throws SQLException {
        Session session = getSession();
        Connection conn = session.connection();
        CallableStatement call = conn.prepareCall("{call sp_jiuqi_note_revoke(?,?,?,?)}");
        call.setInt(1, revokeType);
        call.setLong(2, userId);
        call.setLong(3, id);
        call.registerOutParameter(4, Types.VARCHAR);
        call.execute();
        return call.getString(4);
    }

    private void closeResource(ResultSet rs, Statement s, Connection con) {
        if (rs != null)
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
        if (s != null)
            try {
                s.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
        if (con != null)
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
    }

    @Autowired
    public void setCykmdmysDao(CykmdmysDao cykmdmysDao) {
        this.cykmdmysDao = cykmdmysDao;
    }

    @Autowired
    public void setGsjgService(GsjgService gsjgService) {
        this.gsjgService = gsjgService;
    }
}
